﻿IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Version]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Version](
	[VersionNumber] [varchar](10) NOT NULL,
 CONSTRAINT [PK_Version] PRIMARY KEY CLUSTERED 
(
	[VersionNumber] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SilverlightBridgeUsers]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[SilverlightBridgeUsers](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [nvarchar](50) NOT NULL,
	[Password] [nvarchar](50) NOT NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[Email] [nvarchar](50) NULL,
	[Role] [nvarchar](50) NOT NULL,
	[LoginFailures] [int] NOT NULL,
	[Active] [bit] NOT NULL,
	[SuperUser] [bit] NOT NULL,
	[IPAddress] [nvarchar](50) NULL,
	[SilverlightPassword] [nvarchar](150) NULL,
 CONSTRAINT [PK_SilverlightBridgeUsers] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ApplicationLog]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[ApplicationLog](
	[LogID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [nvarchar](50) NULL,
	[LogDescription] [nvarchar](250) NOT NULL,
	[LogDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ApplicationLog] PRIMARY KEY CLUSTERED 
(
	[LogID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END


IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[ApplicationLog]') AND name = N'IX_ApplicationLog')
CREATE NONCLUSTERED INDEX [IX_ApplicationLog] ON [dbo].[ApplicationLog] 
(
	[LogDate] ASC
) ON [PRIMARY]


/****** Object:  Default [DF_ApplicationLog_LogDate]    Script Date: 07/01/2009 05:12:59 ******/
IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DF_ApplicationLog_LogDate]') AND type = 'D')
BEGIN
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_ApplicationLog_LogDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[ApplicationLog] ADD  CONSTRAINT [DF_ApplicationLog_LogDate]  DEFAULT (getdate()) FOR [LogDate]
END


END

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileUpload_Categories]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[FileUpload_Categories](
	[CategoryID] [int] IDENTITY(1,1) NOT NULL,
	[PortalID] [int] NOT NULL,
	[UserID] [int] NOT NULL,
	[ParentCategoryID] [int] NULL,
	[CategoryName] [nvarchar](50) NULL,
	[Level] [int] NOT NULL,
 CONSTRAINT [PK_FileUpload_Categories] PRIMARY KEY CLUSTERED 
(
	[CategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FileUpload_Categories]') AND name = N'PK_FileUpload_PortalID_UserID')
CREATE NONCLUSTERED INDEX [PK_FileUpload_PortalID_UserID] ON [dbo].[FileUpload_Categories] 
(
	[PortalID] ASC,
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileUpload_Files]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[FileUpload_Files](
	[FileID] [int] IDENTITY(1,1) NOT NULL,
	[FilePath] [nvarchar](1000) NOT NULL,
	[FileName] [nvarchar](150) NOT NULL,
	[OriginalFileName] [nvarchar](150) NOT NULL,
	[UserID] [int] NOT NULL,
	[FolderID] [int] NOT NULL,
 CONSTRAINT [PK_FileUpload_Files] PRIMARY KEY CLUSTERED 
(
	[FileID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END


IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FileUpload_Files]') AND name = N'Ix_FileUpload_FolderID')
CREATE NONCLUSTERED INDEX [Ix_FileUpload_FolderID] ON [dbo].[FileUpload_Files] 
(
	[FolderID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FileUpload_Files]') AND name = N'Ix_FileUpload_UserId')
CREATE NONCLUSTERED INDEX [Ix_FileUpload_UserId] ON [dbo].[FileUpload_Files] 
(
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


/*** This script will only run if the version number in the databse is less than 01.00.00 ***/
if((SELECT count(*) FROM Version) = 0)
BEGIN

	/** Update Version **/
	DELETE FROM Version
	INSERT INTO Version(VersionNumber) VALUES (N'01.00.00')
	
END
